#include "candelabrodb.h"
#include <QFile>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include "ohlcvitem.h"
#include "ohlcvset.h"
#include "QVariant"
#include "QVariantList"
#include <QStringList>
#include <QCoreApplication>
#include <QDebug>



/**
  * Initialize object's variables and some predefinitions.
  */

CandelabroDB::CandelabroDB()
{
    /* beginning of initialization*/
//     this->databaseName = QCoreApplication::applicationDirPath() + "/resources/candelabro.db";
     this->databaseName = "C:/pa2/candelabro/candelabro/resources/candelabro.db";


    this->databaseDriver = "QSQLITE";
    this->connectionName = "candelabro_sqlite";

    this->sqlTableStocks =     "CREATE table if not exists stocks"
                               "("
                               "     id integer primary key autoincrement,"
                               "     name text,"
                               "     description text"
                               ");";

    this->sqlTableStockPrices = "CREATE table if not exists stockprices"
                                "("
                                "     id integer primary key autoincrement,"
                                "     stock_id integer not null,"
                                "     open DECIMAL(5,2) not null ,"
                                "     high DECIMAL(5,2) not null ,"
                                "     low DECIMAL(5,2) not null ,"
                                "     close DECIMAL(5,2) not null ,"
                                "     volume integer not null,"
                                "     unixdate integer not null,"
                                "     FOREIGN KEY(stock_id) REFERENCES stocks(id)"
                                ");";



    this->sqlTableStockPriceExists = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='stockprices';";
    this->sqlTableStocksExists     = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='stocks';";


    this->sqlPragmaConfigsFK = "PRAGMA foreign_keys = ON;";


    //soh adiciona se nao existir
    //if (!this->connectionAlreadyExists())
    this->connection = QSqlDatabase::addDatabase(this->databaseDriver, this->connectionName);

    this->connection.setDatabaseName(databaseName);
    /* end of initialization*/

}


bool CandelabroDB::connectionAlreadyExists(){
    return false;
}

bool CandelabroDB::deleteSet(OHLCVSet set)
{
//    qDebug()<<"Preparing for deletion of previous values.";

    QSqlQuery q = QSqlQuery(this->connection);
    QString sql_delete = "delete from stockprices where (stock_id=?) and (unixdate=?);";
    q.prepare(sql_delete);

//    qDebug()<< "Query prepared, binding sql parameters.";


    QVariantList stock_ids;
    QVariantList unixdates;




    //binding values
    int stock_id = stockId(set.at(0).stockName);
    for (int i=0; i<set.size();i++){
        stock_ids << stock_id;
        unixdates << set.at(i).unixdate;
    }


    q.addBindValue(stock_ids);
    q.addBindValue(unixdates);


//    qDebug()<< "SQL parameters bound, executing batch.";

    if (!q.execBatch()){
        qDebug() << q.lastError();
        return false;
    }

//    qDebug()<< "Batch execution successful.";

    return true;
}


/**
  Falta testar!!
  */
OHLCVSet CandelabroDB::getStockPrices(QString stockName, QDate startDate, QDate endDate)
{

    OHLCVSet set;

//    qDebug()<<"Requesting the stock_id.";
    int stock_id = stockId(stockName);
    if (stock_id == -1) {
        qDebug()<<"The requested stock name was not found in the database.";
        return set;
    }

//    qDebug()<<"Stock_id for "<< stockName <<" found, " << stock_id;

    int startDate_t = QDateTime(startDate).toTime_t();
    int endDate_t = QDateTime(endDate).toTime_t();


//    qDebug()<<"Range dates converted to unix timestamp, preparing query.";

    QString sql_select = "select open, high, low, close, volume, unixdate from stockprices where stock_id = :stock_id and unixdate between :inicio and :fim ;";


    QSqlQuery q = QSqlQuery(this->connection);
    q.prepare(sql_select);

    q.bindValue(":stock_id", stock_id);
    q.bindValue(":inicio", startDate_t);
    q.bindValue(":fim", endDate_t);


//    qDebug()<<"Query prepared and parameters bound, tryng to execute query.";

    if (!q.exec()){
        qDebug()<<"o noes";
        qDebug()<<q.lastError();
        return set;
    }
    else{
        //time to iterate!
        q.first();
        while (q.next()){
            // open, high, low, close, volume, unixdate

            float open   = q.value(0).toFloat();
            float high   = q.value(1).toFloat();
            float low    = q.value(2).toFloat();
            float close  = q.value(3).toFloat();
            float volume = q.value(4).toFloat();
            long  date   = (long) q.value(5).toLongLong();

            const OHLCVItem *aux = new OHLCVItem(new QString(stockName), open, high, low, close, volume, date);
            set.append(*aux);

        }


    }


    return set;

}


/**
  * returns the stocks names in the database
  */
QStringList CandelabroDB::getAllStocksNames()
{
    QString sql = "select s.name, s.description "
                  " from stocks s; ";

    QStringList ret;
    QSqlQuery q = QSqlQuery(this->connection);


    if (!q.exec(sql)){
        qDebug()<<q.lastError();
        return ret;
    }



    while(q.next()){
        ret <<q.value(0).toString();
    }

    return ret;
}


/**
  * returns all the stocks which have prices.
  */
QStringList CandelabroDB::getPricedStocksNames()
{
    QString sql = "select distinct s.name, s.description "
                  " from stockprices sp "
                  " inner join stocks s on (s.id = sp.stock_id)"
                  " order by s.name; ";

    QStringList ret;
    QSqlQuery q = QSqlQuery(this->connection);


    if (!q.exec(sql)){
        qDebug()<<q.lastError();
        return ret;
    }



    while(q.next()){
        ret <<q.value(0).toString();
    }

    return ret;
}



/**
  * Import a OHLCVSet to the database. All the items in the
  * set MUST HAVE the same stockName. Otherwise, they're not stored.
  */
bool CandelabroDB::insertStockPrice(OHLCVSet set){



    if (set.size()==0) return true;

    //check if they're all the same
    QString first = set.at(0).stockName;
    for (int i=0;i<set.size();i++){
        //TODO ensure set equalness
    }

    int stock_id = stockId(first);


    if (!deleteSet(set)){
        return false;
    }


    //TODO
    QSqlQuery q = QSqlQuery(this->connection);
    QString sql_insert = "insert into stockprices"
                        "("
                            "stock_id, "
                            "open, "
                            "high, "
                            "low, "
                            "close, "
                            "volume, "
                            "unixdate"
                        ") "
                        "values "
                        "( ?, ?, ?, ?, ?, ?, ? );";


    q.prepare(sql_insert);

    QVariantList stock_ids;
    QVariantList opens;
    QVariantList highs;
    QVariantList lows;
    QVariantList closes;
    QVariantList volumes;
    QVariantList unixdates;




    for (int i=0; i<set.size();i++){
        stock_ids << stock_id;
        opens << set.at(i).open;
        highs << set.at(i).high;
        lows << set.at(i).low;
        closes << set.at(i).close;
        volumes << set.at(i).volume;
        unixdates << set.at(i).unixdate;
    }

    q.addBindValue(stock_ids);
    q.addBindValue(opens);
    q.addBindValue(highs);
    q.addBindValue(lows);
    q.addBindValue(closes);
    q.addBindValue(volumes);
    q.addBindValue(unixdates);

    if (!q.execBatch()){
        qDebug() << q.lastError();
        return false;
    }

    return true;
}


/**
  * Checks if the database connection is open.
  */
bool CandelabroDB::isDatabaseOpen()
{
    return connection.isOpen();
}

/**
  * Checks if the database file currently exists
  */
bool CandelabroDB::databaseExists()
{
    return QFile::exists(this->databaseName);
}


/**
  * Deletes the database file.
  */
bool CandelabroDB::deleteDatabase()
{
    if (!this->closeDatabase()){
        return false;
    }

    QFile f(this->databaseName);
    if (!f.remove()){
        qDebug()<<f.errorString();
        return false;
    }

    return true;
}


/**
  * Checks if a stock already exists in the database
  */
bool CandelabroDB::existsStock(QString stockName){

    QString sql = "select count(*) from stocks where name=:stockname";
    QSqlQuery q = QSqlQuery(this->connection);
    q.prepare(sql);
    q.bindValue(":stockname", stockName.toUpper());
    if (!q.exec() || !q.next()){
        qDebug()<< q.lastError();
        return false;
    }
    else{
        if (q.value(0).toInt() == 0 ) return false;
    }

    return true;
}


/**
  * Creates the database file, if it doesn't exists yet, and then opens
  * the connection.
  */
bool CandelabroDB::openDatabase()
{

    if(!this->connection.open()){
        qDebug()<< this->connection.lastError();
        return false;
    }
    return true;
}

/**
  * Closes the database
  */
bool CandelabroDB::closeDatabase(){
    this->connection.close();
    //QSqlDatabase::removeDatabase(this->connectionName);
    if(this->connection.isOpen()){
        qDebug()  << this->connection.lastError();
        return false;
    }
    return true;

}


/**
  * Checks if the table 'stockprices' exists in database.
  * Deprecated.
  */
bool CandelabroDB::existsTableStockPrices(){
    QSqlQuery q1 = QSqlQuery(this->sqlTableStockPriceExists,this->connection);
    q1.first();
    if (!q1.isValid()){
        qDebug()<< q1.lastError();
        return false;
    }
    else{
        if (q1.value(0).toInt() == 0 ) return false;
    }

    return true;
}

/**
  * Checks if the table 'stockps' exists in database
  * Deprecated.
  */
bool CandelabroDB::existsTableStocks(){
    //table stocks
    QSqlQuery q = QSqlQuery(this->sqlTableStocksExists,this->connection);
    q.first();
    if(!q.isValid()) {
        qDebug()<< q.lastError();
        return false;
    }
    else{
        if (q.value(0).toInt() == 0 ) {
            return false;
        }
    }
    return true;
}

/**
  * Checks if the database is set up. Deprecated.
  */
bool CandelabroDB::isDatabaseSetup(){
    //TODO
    return true;
}

/**
  * Setup database tables, indexes, and configurations.
  */
bool CandelabroDB::setUpDatabase()
{

    QSqlQuery q = QSqlQuery(this->connection);


    //configs
    if (!q.exec(this->sqlPragmaConfigsFK)){
        qDebug() << q.lastError();
        return false;
    }

    qDebug()<<"Foreign keys enabled.";


    //tables
    if (!q.exec(this->sqlTableStocks)){
        qDebug() << q.lastError();
        return false;
    }

    if (!q.exec(this->sqlTableStockPrices)){
        qDebug() << q.lastError();
        return false;
    }
    qDebug()<<"Tables successfully created.";


    //indexes
    QString sql;
    sql = "create unique index if not exists idx_stocknames on stocks(name);";
    if (!q.exec(sql)){
        qDebug() << q.lastError();
        return false;
    }

    sql = "create unique index if not exists idx_stockdate on stockprices(stock_id,unixdate);";
    if (!q.exec(sql)){
        qDebug() << q.lastError();
        return false;
    }

    qDebug()<<"Indexes successfully created.";

    qDebug()<<"Database successfully set up.";




    return true;

}


/**
  * returns the historic for a stock
  */
OHLCVSet CandelabroDB::selectAll(QString stockName)
{

    QString sql = "select "
                        "open, "
                        "high, "
                        "low, "
                        "close, "
                        "volume, "
                        "unixdate "
                  "from "
                     "  stockprices "
                  "where "
                    "   stockname = :stockname "
                  "order by"
                    "   unixdate;";

    QSqlQuery q = QSqlQuery(this->connection);

    q.prepare(sql);
    q.bindValue(":stockname", stockName);

     OHLCVSet set = OHLCVSet();

    if (!q.exec()){
        qDebug()<<q.lastError();
        return set;
    }


    while (q.next()){
       const OHLCVItem item = OHLCVItem(new QString(stockName),
                                  q.value(0).toDouble(),
                                  q.value(1).toDouble(),
                                  q.value(2).toDouble(),
                                  q.value(3).toDouble(),
                                  q.value(4).toDouble(),
                                  q.value(5).toUInt());
       set.append(item);
    }



    return set;

}



/**
  * Checks if there is a price defined for the stock at a specified date
  */
bool CandelabroDB::existsPrice(QString stockName, uint unixdate){
//TODO




    QString sql = "select count(*)"
                  "from "
                  "   stockprices "
                  "where "
                  "   (stockname = :stockname) and (unixdate=:unixdate)";

    QSqlQuery q = QSqlQuery(this->connection);

    q.prepare(sql);
    q.bindValue(":stockname", stockName);
    q.bindValue(":unixdate", unixdate);

    if (!q.exec() || !q.next()){
        qDebug()<<q.lastError();
        return false;
    }
    else{
        return q.value(0).toInt()>=1;
    }
}


int CandelabroDB::stockId(QString stockName){
    QString sql = "select id from stocks where name=:name;";

    QSqlQuery q = QSqlQuery(this->connection);

    q.prepare(sql);
    q.bindValue(":name", stockName.trimmed().toUpper());

    if (!q.exec()){
        qDebug()<<q.lastError();
        return -1;
    }

    if (q.first()){
        return q.value(0).toInt();
    }
    else {
        return -1;
    }

}


/**
  * Stores a stock with name and description in the database
  */
bool CandelabroDB::insertStock(QString stockName, QString stockDescription){

    QSqlQuery q = QSqlQuery(this->connection);

    QString sql = "insert into stocks"
                        "("
                            "name, "
                            "description "
                        ") "
                        "values "
                        "("
                            ":name, "
                            ":description"
                        ");";


    q.prepare(sql);

    q.bindValue(":name", stockName);
    q.bindValue(":description", stockDescription.trimmed());

    if (!q.exec()){
          qDebug() << q.lastError();
          return false;
    }

    return true;
}

/**
  * Stores a OHLCVItem in the database
  */
bool CandelabroDB::insertStockPrice(OHLCVItem item){
    //TODO
    QSqlQuery q = QSqlQuery(this->connection);

    QString sql = "insert into stockprices"
                        "("
                            "stockname, "
                            "open, "
                            "high, "
                            "low, "
                            "close, "
                            "volume, "
                            "unixdate"
                        ") "
                        "values "
                        "("
                            ":stockname, "
                            ":open, "
                            ":high, "
                            ":low, "
                            ":close, "
                            ":volume, "
                            ":unixdate"
                        ");";

    q.prepare(sql);
    q.bindValue(":stockname", item.stockName);
    q.bindValue(":open",item.open);
    q.bindValue(":high",item.high);
    q.bindValue(":low",item.low);
    q.bindValue(":close",item.close);
    q.bindValue(":volume", item.volume);
    q.bindValue(":unixdate",item.unixdate);

    if (!q.exec()){
          qDebug() << q.lastError();
          return false;
    }
    else return true;
}

